package io.renren.common.utils;


import org.apache.poi.POIXMLDocument;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 导出
 */
public class ExportUtils {

    /**
     * @param request     request
     * @param response    response
     * @param targetPath  导出文件的名字
     * @param sheetName   sheet名字
     * @param length      表格的列数
     * @param width       表格的列的宽度
     * @param height      表格的行的高度
     * @param rethmFisrt  数据list
     * @param columnNames 显示的列名数组
     */
    public static void exportBulk(HttpServletRequest request, HttpServletResponse response, String targetPath,
                                  String sheetName, int length, int width, int height, HashMap<String, Object> rethmFisrt, String[] columnNames) {
        PrintWriter out = null;
        String targetfile = targetPath;// 输出的excel文件名
        // 清除文件
        isExist(getUrlString(request) + targetfile);
        // 根据刚刚的文件地址、创建一个file对象
        File f = new File(getUrlString(request) + targetfile);

        try {

            // 创建Excel的工作书册 Workbook,对应到一个excel文档
            XSSFWorkbook wb = new XSSFWorkbook();
            //工作表名后面的数字，如表1，表2
            int sheetNum = 0;
            //记录总行数
            int rownum = 0;
            //设置每个sheet最多多少行
            int page = 1000000;
            //记录每个sheet的行数
            int tempnum = 0;
            while (true) {
                sheetNum++;
                rownum++;
                // 创建Excel的工作sheet,对应到一个excel文档的tab
                XSSFSheet sheet = wb.createSheet(sheetName + sheetNum);
                // 设置列的宽度
                for (int i = 0; i < length; i++) {
                    sheet.setColumnWidth(i, width);
                }

                // 设置单元格样式
                XSSFCellStyle style = wb.createCellStyle();
                // 设置居中
                style.setAlignment(XSSFCellStyle.ALIGN_CENTER);
                style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);
                style.setBorderBottom(XSSFCellStyle.BORDER_THIN); // 下边框
                style.setBorderLeft(XSSFCellStyle.BORDER_THIN);// 左边框
                style.setBorderTop(XSSFCellStyle.BORDER_THIN);// 上边框
                style.setBorderRight(XSSFCellStyle.BORDER_THIN);// 右边框

                // 外表数据
                HashMap<String, Object> rethm = rethmFisrt;
                List<Object> myList = null;
                Object[] arr = new Object[length];
                myList = (List<Object>) rethm.get("detailsList");

                if (myList != null && !myList.isEmpty()) {
                    //第一行行头(即例标题)
                    int i = 0;
                    XSSFRow row = sheet.createRow(i);
                    row.setHeight((short) height);// 设定行的高度
                    XSSFCell cell = null;
                    for (int k = 0; k < length - 1; k++) {
                        cell = row.createCell(k);
                        cell.setCellStyle(style);
                        cell.setCellValue(columnNames[k]);
                    }
                    tempnum = 0;
                    for (int j = rownum - sheetNum; j < myList.size(); j++) {
                        rownum++;
                        arr = (Object[]) myList.get(j);
                        row = sheet.createRow(++tempnum);
                        for (int k = 0; k < length - 1; k++) {
                            cell = row.createCell(k);
                            cell.setCellStyle(style);
                            cell.setCellValue(String.valueOf((arr[k] == null || arr[k].equals("")) ? ""
                                    : arr[k]));
                        }
                        if (rownum % page == 0) {
                            break;
                        }
                    }
                }
                if ((rownum - sheetNum) == myList.size()) {
                    break;
                }
            }
            FileOutputStream os = new FileOutputStream(f);
            wb.write(os);
            os.close();

            // 如果文件不存在
            if (!f.exists()) {
                response.sendError(404, "File not found!");
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
                        /*
                         * if (f.exists()) {// 下载完毕删除文件 f.delete(); }
			 */
        }
    }

    /**
     * 删除文件
     */
    public static void isExist(String path) {
        File file = new File(path);
        // 判断文件夹是否存在,如果不存在则创建文件夹
        if (file.exists()) {
            file.delete();
        }
    }

    /**
     * 文件存储路径
     *
     * @return String url 文件存储路径
     */
    @SuppressWarnings("deprecation")
    public static String getUrlString(HttpServletRequest request) {
        String url = request.getRealPath("/") + "excel//";
        File file = new File(url);
        // 如果文件夹不存在则创建文件夹
        if (!file.exists()) {
            file.mkdir();
        }
        return url;
    }


    public static Workbook create(InputStream inp) throws Exception {
        if (!inp.markSupported()) {
            inp = new PushbackInputStream(inp, 8);
        }
        if (POIFSFileSystem.hasPOIFSHeader(inp)) {
            System.out.println("2003及以下");
            return new HSSFWorkbook(inp);
        }
        if (POIXMLDocument.hasOOXMLHeader(inp)) {
            System.out.println("2007及以上");
            return new XSSFWorkbook(OPCPackage.open(inp));
        }
        System.out.println("你的excel版本目前poi解析不了");
        return null;
    }


}
